package com.bringway.p2p.dao.impl;

import java.sql.SQLClientInfoException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.context.annotation.EnableLoadTimeWeaving;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.framework.dao.ZhfwJdbcDao;
import com.bringway.framework.util.RequestUtils;
import com.bringway.global.util.StringUtils;
import com.bringway.p2p.dao.P2PHBDao;

import net.sf.json.JSONObject;

/*
 * @author fan
 * @className P2PHBDaoImpl
 * @description 红包明细持久层dao实现类
 * @date 2016年1月5日上午10:21:59
 */
@Repository
public class P2PHBDaoImpl extends P2PJdbcDao implements P2PHBDao {
	
	Logger logger = Logger.getLogger(this.getClass().getName());
	/*
	 * (non-Javadoc)
	 * 
	 * @see com.bringway.p2p.dao.P2PHBDao#queryHBDetails(net.sf.json.JSONObject)
	 */
	@Override
	public List queryHBDetails(JSONObject parms) {
		String USER_ID = parms.optString("USER_ID");
		String HB_USE_DATE_START = parms.optString("HB_USE_DATE_START");
		String HB_USE_DATE_END = parms.optString("HB_USE_DATE_END");
		String HB_USE_CONDITION_MIN_JE = parms.optString("HB_USE_CONDITION_MIN_JE");
		String HB_STATE = parms.optString("HB_STATE");
		String HB_EFFECTIVE_DAYS = parms.optString("HB_EFFECTIVE_DAYS");
		String HB_JE = parms.optString("HB_JE");
		String HB_CREATE_DATE_START = parms.optString("HB_CREATE_DATE_START");
		String HB_CREATE_DATE_END = parms.optString("HB_CREATE_DATE_END");
		String USER_ID_LXFS = parms.optString("USER_ID_LXFS");
		int start = parms.optInt("start");
		int limit = parms.optInt("limit");
		StringBuffer sql = new StringBuffer();
		sql.append(
				" SELECT v.ID,v.HB_JE,DATE_FORMAT(v.HB_CREATE_DATE,'%Y-%m-%d %T') as HB_CREATE_DATE,v.HB_EFFECTIVE_DAYS,DATE_FORMAT(v.HB_OVER_DATE,'%Y-%m-%d %T') as HB_OVER_DATE,v.HB_STATE,	v.HB_USE_CONDITION_MIN_JE,DATE_FORMAT(v.HB_USE_DATE,'%Y-%m-%d %T') as HB_USE_DATE,v.REF_TZRXX_ID,v.USER_ID,v2.SJHM as USER_ID_LXFS from v_p2p_hb v "
				);
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		sql.append(" LEFT JOIN v_p2p_user_tjr v2 ON ( 1=1  ");
		if (StringUtils.isNotEmpty(USER_ID_LXFS)) {
			sql.append(" and v2.SJHM like :SJHM ");
			parmsMap.put("SJHM", "%"+USER_ID_LXFS+"%");
		}
		sql.append("  and v.USER_ID = v2.USER_NAME) ");
		sql.append(" where 1=1 ");
		if (StringUtils.isNotEmpty(USER_ID_LXFS)) {
			sql.append(" and USER_ID in (select v3.USER_NAME from v_p2p_user_tjr v3 where v3.SJHM like :SJHM ) ");
		}
		if (StringUtils.isNotEmpty(USER_ID)) {
			sql.append(" and USER_ID like :USER_ID ");
			parmsMap.put("USER_ID", "%" + USER_ID + "%");
		}
		if (StringUtils.isNotEmpty(HB_USE_DATE_START)) {
			sql.append(" and HB_USE_DATE >= :HB_USE_DATE_START ");
			parmsMap.put("HB_USE_DATE_START", HB_USE_DATE_START+" 00:00:00");
		}
		if (StringUtils.isNotEmpty(HB_USE_DATE_END)) {
			sql.append(" and HB_USE_DATE <= :HB_USE_DATE_END ");
			parmsMap.put("HB_USE_DATE_END", HB_USE_DATE_END+" 23:59:59");
		}
		if (StringUtils.isNotEmpty(HB_CREATE_DATE_START)) {
			sql.append(" and HB_CREATE_DATE >= :HB_CREATE_DATE_START ");
			parmsMap.put("HB_CREATE_DATE_START", HB_CREATE_DATE_START+" 00:00:00");
		}
		if (StringUtils.isNotEmpty(HB_CREATE_DATE_END)) {
			sql.append(" and HB_CREATE_DATE <= :HB_CREATE_DATE_END ");
			parmsMap.put("HB_CREATE_DATE_END", HB_CREATE_DATE_END+" 23:59:59");
		}
		if (StringUtils.isNotEmpty(HB_USE_CONDITION_MIN_JE)) {
			sql.append(" and HB_USE_CONDITION_MIN_JE = :HB_USE_CONDITION_MIN_JE ");
			parmsMap.put("HB_USE_CONDITION_MIN_JE", HB_USE_CONDITION_MIN_JE);
		}
		if (StringUtils.isNotEmpty(HB_STATE)) {
			sql.append(" and HB_STATE = :HB_STATE ");
			parmsMap.put("HB_STATE", HB_STATE);
		}
		if (StringUtils.isNotEmpty(HB_EFFECTIVE_DAYS)) {
			sql.append(" and HB_EFFECTIVE_DAYS = :HB_EFFECTIVE_DAYS ");
			parmsMap.put("HB_EFFECTIVE_DAYS", HB_EFFECTIVE_DAYS);
		}
		if (StringUtils.isNotEmpty(HB_JE)) {
			sql.append(" and HB_JE = :HB_JE ");
			parmsMap.put("HB_JE", HB_JE);
		}
		sql.append("order by v.HB_CREATE_DATE DESC ");
		if(0 != limit){
			sql.append(" limit :start,:limit ");
			parmsMap.put("start", start);
			parmsMap.put("limit", limit);
		}
		logger.info("queryHBDetails sql is : "+ sql.toString());
		logger.info("queryHBDetails parmsMap is : "+ parmsMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), parmsMap);
	}

	/*
	 * (non-Javadoc)
	 * @see com.bringway.p2p.dao.P2PHBDao#queryHBDetailsCount(net.sf.json.JSONObject)
	 */
	@Override
	public int queryHBDetailsCount(JSONObject parms) {
		String USER_ID = parms.optString("USER_ID");
		String HB_USE_DATE_START = parms.optString("HB_USE_DATE_START");
		String HB_USE_DATE_END = parms.optString("HB_USE_DATE_END");
		String HB_USE_CONDITION_MIN_JE = parms.optString("HB_USE_CONDITION_MIN_JE");
		String HB_STATE = parms.optString("HB_STATE");
		String HB_EFFECTIVE_DAYS = parms.optString("HB_EFFECTIVE_DAYS");
		String HB_JE = parms.optString("HB_JE");
		String HB_CREATE_DATE_START = parms.optString("HB_CREATE_DATE_START");
		String HB_CREATE_DATE_END = parms.optString("HB_CREATE_DATE_END");
		String USER_ID_LXFS = parms.optString("USER_ID_LXFS");
		StringBuffer sql = new StringBuffer();
		sql.append(
				" SELECT count(*) from v_p2p_hb v ");
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		sql.append(" LEFT JOIN v_p2p_user_tjr v2 ON ( 1=1  ");
		if (StringUtils.isNotEmpty(USER_ID_LXFS)) {
			sql.append(" and v2.SJHM like :SJHM ");
			parmsMap.put("SJHM", "%"+USER_ID_LXFS+"%");
		}
		sql.append("  and v.USER_ID = v2.USER_NAME) ");
		sql.append(" where 1=1 ");
		if (StringUtils.isNotEmpty(USER_ID_LXFS)) {
			sql.append(" and USER_ID in (select v3.USER_NAME from v_p2p_user_tjr v3 where v3.SJHM like :SJHM ) ");
		}
		if (StringUtils.isNotEmpty(USER_ID)) {
			sql.append(" and USER_ID like :USER_ID ");
			parmsMap.put("USER_ID", "%" + USER_ID + "%");
		}
		if (StringUtils.isNotEmpty(HB_USE_DATE_START)) {
			sql.append(" and HB_USE_DATE >= :HB_USE_DATE_START ");
			parmsMap.put("HB_USE_DATE_START", HB_USE_DATE_START+" 00:00:00");
		}
		if (StringUtils.isNotEmpty(HB_USE_DATE_END)) {
			sql.append(" and HB_USE_DATE <= :HB_USE_DATE_END ");
			parmsMap.put("HB_USE_DATE_END", HB_USE_DATE_END+" 23:59:59");
		}
		if (StringUtils.isNotEmpty(HB_CREATE_DATE_START)) {
			sql.append(" and HB_CREATE_DATE >= :HB_CREATE_DATE_START ");
			parmsMap.put("HB_CREATE_DATE_START", HB_CREATE_DATE_START+" 00:00:00");
		}
		if (StringUtils.isNotEmpty(HB_CREATE_DATE_END)) {
			sql.append(" and HB_CREATE_DATE <= :HB_CREATE_DATE_END ");
			parmsMap.put("HB_CREATE_DATE_END", HB_CREATE_DATE_END+" 23:59:59");
		}
		if (StringUtils.isNotEmpty(HB_USE_CONDITION_MIN_JE)) {
			sql.append(" and HB_USE_CONDITION_MIN_JE = :HB_USE_CONDITION_MIN_JE ");
			parmsMap.put("HB_USE_CONDITION_MIN_JE", HB_USE_CONDITION_MIN_JE);
		}
		if (StringUtils.isNotEmpty(HB_STATE)) {
			sql.append(" and HB_STATE = :HB_STATE ");
			parmsMap.put("HB_STATE", HB_STATE);
		}
		if (StringUtils.isNotEmpty(HB_EFFECTIVE_DAYS)) {
			sql.append(" and HB_EFFECTIVE_DAYS = :HB_EFFECTIVE_DAYS ");
			parmsMap.put("HB_EFFECTIVE_DAYS", HB_EFFECTIVE_DAYS);
		}
		if (StringUtils.isNotEmpty(HB_JE)) {
			sql.append(" and HB_JE = :HB_JE ");
			parmsMap.put("HB_JE", HB_JE);
		}
		sql.append(" order by v.HB_CREATE_DATE DESC ");
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), parmsMap,Integer.class);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.bringway.p2p.dao.P2PHBDao#queryHBgltzrxx(net.sf.json.JSONObject)
	 */
	@Override
	public List queryHBgltzrxx(JSONObject parms) {
		String id = parms.optString("id");
		StringBuffer sql = new StringBuffer();
		sql.append(
				" select v.ID,v.USERID,v.ZQID,v.TCJE,v.TZSJ,v.TCZT,v.HKZT,v.JXRQ,v.TZSY from v_p2p_tzrxx v where id = :id  ");
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("id", id);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

}
